﻿using System;
using System.Collections.ObjectModel;
using System.Data.SQLite;
using CSharp.Libs.Koc.KernelModels.ItemClasses;
using CSharp.Libs.Koc;

namespace CSharp.Libs.Koc.KernelModels
{
    public partial class DatabaseOperations
    {
        #region declarations

        #region events
        #endregion
        #endregion



        #region event handling
        #endregion



        #region private functions
        /// <summary>_MarchesItemExists</summary>
        /// <param name="ServerId"></param>
        /// <param name="CityId"></param>
        /// <returns></returns>
        /// <history>15.05.2011</history>
        private Boolean _MarchesItemExists(
            Int32 MarchID)
        {
            try
            {
                Int32 intServerID = Int32.Parse(_AB.TM.CurrentServerId);

                String strCMD = String.Concat(
                    "SELECT * FROM tblMarches WHERE ServerID=",
                    intServerID.ToString(),
                    " AND MarchID=",
                    MarchID.ToString());
                SQLiteCommand CMD = new SQLiteCommand(strCMD, _CON);

                SQLiteDataReader DR = CMD.ExecuteReader(System.Data.CommandBehavior.Default);
                
                if(DR.HasRows)
                {
                    DR.Close();
                    return true;
                }

                DR.Close();
            }
            catch(Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return false;
        }

        /// <summary>_MarchesGetItem</summary>
        /// <param name="MarchID"></param>
        /// <returns></returns>
        /// <history>23.05.2011</history>
        private MarchItem _MarchesGetItem(
            Int32 MarchID)
        {
            try
            {
                Int32 intServerID = Int32.Parse(_AB.TM.CurrentServerId);
                
                String strCMD = String.Concat(
                    "SELECT * FROM tblMarches WHERE ServerID=",
                    intServerID.ToString(),
                    " AND MarchID=",
                    MarchID.ToString());
                SQLiteCommand CMD = new SQLiteCommand(strCMD, _CON);

                SQLiteDataReader DR = CMD.ExecuteReader(System.Data.CommandBehavior.Default);

                if (DR.HasRows)
                {
                    return _DataReader2MarchItem(DR);
                }

                DR.Close();
            }
            catch (Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return null;
        }

        /// <summary>_MarchesGetItems</summary>
        /// <param name="ServerId"></param>
        /// <param name="CityId"></param>
        /// <returns></returns>
        /// <history>15.05.2011</history>
        private ObservableCollection<MarchItem> _MarchesGetItems(
            Int32 CityId)
        {
            try
            {
                //Get Item
                String strCMD = "";
                SQLiteCommand CMD = new SQLiteCommand(strCMD, _CON);
                ObservableCollection<MarchItem> ocMarchItems = new ObservableCollection<MarchItem>();
                Int32 intServerID = Int32.Parse(_AB.TM.CurrentServerId);

                if(CityId > 0)
                {
                    strCMD = String.Concat(
                        "SELECT * FROM tblAutoTransport WHERE ServerID=",
                        intServerID.ToString(),
                        " AND CityID=",
                        CityId.ToString());
                }
                else
                {
                    strCMD = String.Concat(
                        "SELECT * FROM tblAutoTransport WHERE ServerID=",
                        intServerID.ToString());
                }
                CMD.CommandText = strCMD;

                using(SQLiteDataReader DR = CMD.ExecuteReader(System.Data.CommandBehavior.Default))
                {
                    if(DR.HasRows)
                    {
                        Boolean fData = true;

                        while(fData)
                        {
                            MarchItem MI = _DataReader2MarchItem(DR);

                            if (MI != null)
                            {
                                ocMarchItems.Add(MI);
                            }

                            fData = DR.NextResult();
                        }
                    }

                    DR.Close();
                }
            }
            catch(Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return null;
        }

        /// <summary>_DataReader2MarchItem</summary>
        /// <param name="DA"></param>
        /// <returns></returns>
        /// <history>23.05.2011</history>
        private MarchItem _DataReader2MarchItem(
            SQLiteDataReader DR)
        {
            try
            {
                MarchItem MI = new MarchItem(_AB, DR);

                //MI.marchId = (Int32)_GetInt64(DR[constants.KEY_MARCH_ID]);
                //MI.tileId = (Int32)_GetInt64(DR[constants.KEY_TILE_ID]);
                //MI.tileType = (Int32)_GetInt64(DR[constants.KEY_TILE_TYPE]);
                //MI.tileLevel = (Int32)_GetInt64(DR[constants.KEY_TILE_LEVEL]);
                //MI.initTS = (Int32)_GetInt64(DR[constants.KEY_INIT_TS]);
                //MI.distance = _GetDouble(DR[constants.KEY_DISTANCE]);
                //MI.speed = (Int32)_GetInt64(DR[constants.KEY_SPEED]);
                //MI.eta = (Int32)_GetInt64(DR[constants.KEY_ETA]);
                //MI.delay = (Int32)_GetInt64(DR[constants.KEY_DELAY]);
                //MI.liftFog = (Int32)_GetInt64(DR[constants.KEY_LIFT_FOG]);
                //MI.atkBoostTime = (Int32)_GetInt64(DR[constants.KEY_ATK_BOOST_TIME]);
                //MI.atk2BoostTime = (Int32)_GetInt64(DR[constants.KEY_ATK2_BOOST_TIME]);
                //MI.defBoostTime = (Int32)_GetInt64(DR[constants.KEY_DEF_BOOST_TIME]);
                //MI.def2BoostTime = (Int32)_GetInt64(DR[constants.KEY_DEF2_BOOST_TIME]);
                //MI.knightCombatBoostTime = (Int32)_GetInt64(DR[constants.KEY_KNIGHT_COMBAT_BOOST_TIME]);
                //MI.marchUnixTime = (Int32)_GetInt64(DR[constants.KEY_MARCH_UNIX_TIME]);
                //MI.reqmicrotime = (Int32)_GetInt64(DR[constants.KEY_REQ_MICRO_TIME]);
                //MI.resmicrotime = (Int32)_GetInt64(DR[constants.KEY_RES_MICRO_TIME]);
                //MI.CityID = (Int32)_GetInt64(DR[URLHolder.PARAM_CID]);
                //MI.MarchType = (Int32)_GetInt64(DR[URLHolder.PARAM_TYPE]);
                //MI.KnightID = (Int32)_GetInt64(DR[URLHolder.PARAM_KID]);
                //MI.DestinationXCoord = (Int32)_GetInt64(DR[URLHolder.PARAM_X_COORD]);
                //MI.DestinationYCoord = (Int32)_GetInt64(DR[URLHolder.PARAM_Y_COORD]);
                //MI.Gold = (Int32)_GetInt64(DR[URLHolder.PARAM_GOLD]);
                //MI.R1 = (Int32)_GetInt64(DR[URLHolder.PARAM_R1]);
                //MI.R2 = (Int32)_GetInt64(DR[URLHolder.PARAM_R2]);
                //MI.R3 = (Int32)_GetInt64(DR[URLHolder.PARAM_R3]);
                //MI.R4 = (Int32)_GetInt64(DR[URLHolder.PARAM_R4]);
                //MI.Items = DR[URLHolder.PARAM_ITEMS].ToString();
                
                //for (Int32 IDX = 0; IDX < 12; IDX++)
                //{
                //    MI.UnitCount[IDX] = (Int32)_GetInt64(DR["u" + (IDX + 1).ToString()]);
                //}                

                //MI.U1 = (Int32)_GetInt64(DR["u1"]);
                //MI.U2 = (Int32)_GetInt64(DR["u2"]);
                //MI.U3 = (Int32)_GetInt64(DR["u3"]);
                //MI.U4 = (Int32)_GetInt64(DR["u4"]);
                //MI.U5 = (Int32)_GetInt64(DR["u5"]);
                //MI.U6 = (Int32)_GetInt64(DR["u6"]);
                //MI.U7 = (Int32)_GetInt64(DR["u7"]);
                //MI.U8 = (Int32)_GetInt64(DR["u8"]);
                //MI.U9 = (Int32)_GetInt64(DR["u9"]);
                //MI.U10 = (Int32)_GetInt64(DR["u10"]);
                //MI.U11 = (Int32)_GetInt64(DR["u11"]);
                //MI.U12 = (Int32)_GetInt64(DR["u12"]);

                return MI;
            }
            catch (Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return null;
        }

        /// <summary>_MarchesCreateItem</summary>
        /// <param name="ServerId"></param>
        /// <param name="CityId"></param>
        /// <param name="ATCI"></param>
        /// <returns></returns>
        /// <remarks>Tested 19.05.2011: Worked and wrote one line to db</remarks>
        /// <history>19.05.2011</history>
        private Boolean _MarchesCreateItem(
            MarchItem MI)
        {
            try
            {
                String strCMD = String.Concat(
                    "INSERT INTO tblMarches(",
                    "MarchID, ",
                    "TileID, ",
                    "TileType, ",
                    "TileLevel, ",
                    "InitTS, ",
                    "Distance, ",
                    "Speed, ",
                    "ETA, ",
                    "Delay, ",
                    "LiftFog, ",
                    "AtkBoostTime, ",
                    "Atk2BoostTime, ",
                    "DefBoostTime, ",
                    "Def2BoostTime, ",
                    "KnightCombatBoostTime, ",
                    "MarchUnixTime, ",
                    "ReqMicroTime, ",
                    "ResMicroTime, ",
                    "ServerID, ",
                    "CityID, ",
                    "KnightID, ",
                    "DestinationXCoord, ",
                    "DestinationYCoord, ",
                    "MarchType, ",
                    "Gold, ",
                    "R1, ",
                    "R2, ",
                    "R3, ",
                    "R4, ",
                    "Items, ",
                    "U1, ",
                    "U2, ",
                    "U3, ",
                    "U4, ",
                    "U5, ",
                    "U6, ",
                    "U7, ",
                    "U8, ",
                    "U9, ",
                    "U10, ",
                    "U11, ",
                    "U12",
                    ") VALUES (",
                    MI.marchId.ToString(), ", ",
                    MI.tileId.ToString(), ", ",
                    MI.tileType.ToString(), ", ",
                    MI.tileLevel.ToString(), ", ",
                    MI.initTS.ToString(), ", ",
                    MI.distance.ToString(), ", ",
                    MI.speed.ToString(), ", ",
                    MI.eta.ToString(), ", ",
                    MI.delay.ToString(), ", ",
                    MI.liftFog.ToString(), ", ",
                    MI.atkBoostTime.ToString(), ", ",
                    MI.atk2BoostTime.ToString(), ", ",
                    MI.defBoostTime.ToString(), ", ",
                    MI.def2BoostTime.ToString(), ", ",
                    MI.knightCombatBoostTime.ToString(), ", ",
                    MI.marchUnixTime.ToString(), ", ",
                    MI.reqmicrotime.ToString(), ", ",
                    MI.resmicrotime.ToString(), ", ",
                    _AB.TM.CurrentServerId, ", ",
                    MI.CityID.ToString(), ", ",
                    MI.KnightID.ToString(), ", ",
                    MI.DestinationXCoord.ToString(), ", ",
                    MI.DestinationYCoord.ToString(), ", ",
                    MI.MarchType.ToString(), ", ",
                    MI.Gold.ToString(), ", ",
                    MI.R1.ToString(), ", ",
                    MI.R2.ToString(), ", ",
                    MI.R3.ToString(), ", ",
                    MI.R4.ToString(), ", ",
                    "'", MI.Items, "', ",
                    MI.U1.ToString(), ", ",
                    MI.U2.ToString(), ", ",
                    MI.U3.ToString(), ", ",
                    MI.U4.ToString(), ", ",
                    MI.U5.ToString(), ", ",
                    MI.U6.ToString(), ", ",
                    MI.U7.ToString(), ", ",
                    MI.U8.ToString(), ", ",
                    MI.U9.ToString(), ", ",
                    MI.U10.ToString(), ", ",
                    MI.U11.ToString(), ", ",
                    MI.U12.ToString(), "",
                    ");");

                SQLiteCommand CMD = new SQLiteCommand(strCMD, _CON);

                Int32 Count = CMD.ExecuteNonQuery();

                if (Count > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return false;
        }

        /// <summary>_MarchUpdateItem</summary>
        /// <param name="ServerId"></param>
        /// <param name="CityId"></param>
        /// <param name="ATCI"></param>
        /// <returns></returns>
        /// <history>19.05.2011</history>
        private Boolean _MarchesUpdateItem(
            MarchItem MI)
        {
            try
            {
                String strCMD = String.Concat(
                "UPDATE tblMarches SET ",
                "TileID = ", MI.tileId.ToString(), ", ",
                "TileType = ", MI.tileType.ToString(), ", ",
                "TileLevel = ", MI.tileLevel.ToString(), ", ",
                "InitTS = ", MI.initTS.ToString(), ", ",
                "Distance = ", MI.distance.ToString(), ", ",
                "Speed = ", MI.speed.ToString(), ", ",
                "ETA = ", MI.eta.ToString(), ", ",
                "Delay = ", MI.delay.ToString(), ", ",
                "LiftFog = ", MI.liftFog.ToString(), ", ",
                "AtkBoostTime = ", MI.atkBoostTime.ToString(), ", ",
                "Atk2BoostTime = ", MI.atk2BoostTime.ToString(), ", ",
                "DefBoostTime = ", MI.defBoostTime.ToString(), ", ",
                "Def2BoostTime = ", MI.def2BoostTime.ToString(), ", ",
                "KnightCombatBoostTime = ", MI.knightCombatBoostTime.ToString(), ", ",
                "MarchUnixTime = ", MI.marchUnixTime.ToString(), ", ",
                "ReqMicroTime = ", MI.reqmicrotime.ToString(), ", ",
                "ResMicroTime = ", MI.resmicrotime.ToString(), ", ",
                "CityID = ", MI.CityID.ToString(), ", ",
                "KnightID = ", MI.KnightID.ToString(), ", ",
                "DestinationXCoord = ", MI.DestinationXCoord.ToString(), ", ",
                "DestinationYCoord = ", MI.DestinationYCoord.ToString(), ", ",
                "MarchType = ", MI.MarchType.ToString(), ", ",
                "Gold = ", MI.Gold.ToString(), ", ",
                "R1 = ", MI.R1.ToString(), ", ",
                "R2 = ", MI.R2.ToString(), ", ",
                "R3 = ", MI.R3.ToString(), ", ",
                "R4 = ", MI.R4.ToString(), ", ",
                "Items = '", MI.Items, "', ",
                "U1 = ", MI.U1.ToString(), ", ",
                "U2 = ", MI.U2.ToString(), ", ",
                "U3 = ", MI.U3.ToString(), ", ",
                "U4 = ", MI.U4.ToString(), ", ",
                "U5 = ", MI.U5.ToString(), ", ",
                "U6 = ", MI.U6.ToString(), ", ",
                "U7 = ", MI.U7.ToString(), ", ",
                "U8 = ", MI.U8.ToString(), ", ",
                "U9 = ", MI.U9.ToString(), ", ",
                "U10 = ", MI.U10.ToString(), ", ",
                "U11 = ", MI.U11.ToString(), ", ",
                "U12 = ", MI.U12.ToString(), "",
                " WHERE ServerID = ", _AB.TM.CurrentServerId,
                " AND MarchID = ",MI.marchId.ToString(),
                ";");

                SQLiteCommand CMD = new SQLiteCommand(strCMD, _CON);
                
                Int32 Count = CMD.ExecuteNonQuery();
                
                if (Count > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return false;
        }

        /// <summary>_MarchUpdateItems</summary>
        /// <param name="Items"></param>
        /// <returns></returns>
        /// <history>19.05.2011</history>
        private Boolean _MarchesUpdateItems(
            ObservableCollection<MarchItem> Items)
        {
            try
            {
                foreach (MarchItem MI in Items)
                {
                    if (MI.ItemChanged)
                    {
                        if (_MarchesUpdateItem(MI))
                        {
                            MI.ItemChanged = false;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                _AB.EC.AddException(ex, this.ToString(), _AB.EC.Tools.GetCurrentMethodName());
            }

            return false;
        }
        #endregion



        #region interface
        #region properties
        #endregion

        #region methods
        public Boolean MarchesItemExists(
            Int32 MarchId)
        {
            return _MarchesItemExists(MarchId);
        }

        public MarchItem MarchesGetItem(
            Int32 MarchID)
        {
            return _MarchesGetItem(MarchID);
        }

        public ObservableCollection<MarchItem> MarchesGetItems()
        {
            return _MarchesGetItems(0);
        }

        public ObservableCollection<MarchItem> MarchesGetItems(
            Int32 CityId)
        {
            return _MarchesGetItems(CityId);
        }

        public Boolean MarchesCreateItem(
            MarchItem MI)
        {
            return _MarchesCreateItem(MI);
        }

        public Boolean MarchesUpdateItem(
            MarchItem MI)
        {
            return _MarchesUpdateItem(MI);
        }
        
        public Boolean MarchesUpdateItems(
            ObservableCollection<MarchItem> Items)
        {
            return _MarchesUpdateItems(Items);
        }
        #endregion
        #endregion
    }
}
